/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package hk.ces.oxfam.patch.OTWTeamCoordinator.importCoordinator;

import hk.ces.oxfam.patch.OTWTeamCoordinator.CompanyItem;
import hk.ces.oxfam.patch.OTWTeamCoordinator.JDBCUtil;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

/**
 *
 * @author ivanlin
 */
public class CompanyDAOUtil
{
  private Connection conn;
  private PreparedStatement pst;
  private ResultSet rs;

  public CompanyDAOUtil()
  {

  }

  public void insertToDb(List<CompanyItem> items) throws SQLException
  {
    this.conn = JDBCUtil.getConnection();
    int updateCount = 0;
    int insertCount = 0;
    for (CompanyItem item : items)
    {
      if (this.isThisRecordAlreadyInExist(item.getId()))
      {
        updateCount += this.executeUpdate(item);
      }
      else
      {
        insertCount += this.executeInseert(item);
      }
    }

    System.out.println("update count : " + updateCount);
    System.out.println("insert count : " + insertCount);

    this.resetTableStartId();
    
    JDBCUtil.close(conn, pst, rs);
  }

  private void resetTableStartId()
  {
    int startId = 1;
    try 
    {
      String sql = "select max(id) from company";
      this.pst = this.conn.prepareStatement(sql);
      this.rs = this.pst.executeQuery();

      while (this.rs.next())
      {
        startId = rs.getInt(1) + 1;
      }
    }
    catch (SQLException ex)
    {
      
      Logger.getLogger(CompanyDAOUtil.class.getName()).log(Level.SEVERE, null, ex);
    }


    String sql = "alter table company alter id restart with " + startId;
    try
    {
      this.pst = this.conn.prepareStatement(sql);
      this.pst.executeUpdate();
    }
    catch (SQLException ex)
    {
      Logger.getLogger(CompanyDAOUtil.class.getName()).log(Level.SEVERE, null, ex);
    }
  }

  private int executeInseert(CompanyItem item)
  {
    try
    {
      String sql = "Insert into company(id, nameen, namech, disabled) values(?,?,?,0)";
      this.pst = this.conn.prepareStatement(sql);
      this.pst.setInt(1, item.getId());
      this.pst.setString(2, item.getNameEn());
      this.pst.setString(3, item.getNameCH());

      return this.pst.executeUpdate();
    }
    catch (SQLException ex)
    {
      Logger.getLogger(ImportDummyData.class.getName()).log(Level.SEVERE, null, ex);
      return 0;
    }
    finally
    {
      JDBCUtil.releaseCursor(pst, rs);
    }
  }

  private int executeUpdate(CompanyItem item)
  {
    try
    {
      String sql = "update company set nameen=?, namech=?, disabled=0 where id = " + item.getId();
      this.pst = this.conn.prepareStatement(sql);
      this.pst.setString(1, item.getNameEn());
      this.pst.setString(2, item.getNameCH());

      return this.pst.executeUpdate();
    }
    catch (SQLException ex)
    {
      Logger.getLogger(ImportDummyData.class.getName()).log(Level.SEVERE, null, ex);
      return 0;
    }
    finally
    {
      JDBCUtil.releaseCursor(pst, rs);
    }
  }

  private boolean isThisRecordAlreadyInExist(int id)
  {
    try
    {
      String sql = "select id from company where id = " + id;

      this.pst = this.conn.prepareStatement(sql);
      this.rs = this.pst.executeQuery();

      while (rs.next())
      {
        return true;
      }
    }
    catch (SQLException ex)
    {
      Logger.getLogger(ImportDummyData.class.getName()).log(Level.SEVERE, null, ex);
    }
    finally
    {
      JDBCUtil.releaseCursor(pst, rs);
    }

    return false;
  }
}
